﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data;
using System.Web.UI.WebControls;
using System.Collections;
using System.Web.UI;

/// <summary>
///DBOperator 的摘要说明
///数据库操作基础类，包括基本的导入导出，其他业务逻辑在此基础上扩展。
/// </summary>
public class DBOperator
{
    private string s_result_message;
    private string s_insert_message;
    private string s_update_message;
    private string s_error_message;

	public DBOperator()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}
    public string ResultMessage
    {
        get { return s_result_message; }
        set { if (s_result_message != value) { s_result_message = value; } }
    }
    public string InsertMessage
    {
        get { return s_insert_message; }
        set { if (s_insert_message != value) { s_insert_message = value; } }
    }
    public string UpdateMessage
    {
        get { return s_update_message; }
        set { if (s_update_message != value) { s_update_message = value; } }
    }
    public string ErrorMessage
    {
        get { return s_error_message; }
        set { if (s_error_message != value) { s_error_message = value; } }
    }
    public string SingleImport(SingleRecord sr)
    {
        //string type = sr.GetType().ToString();
        string connStr = WebConfigurationManager.ConnectionStrings["ConnectionStringForOracle"].ConnectionString;
        string commStr = sr.InsertStr;
        
        OracleConnection conn = new OracleConnection(connStr);

        try
        {
            conn.Open();
            if (sr.isNotEmpty())
            {
                OracleCommand selectCmd = new OracleCommand(sr.SelectStr, conn);
                int count = Convert.ToInt32(selectCmd.ExecuteScalar());
                if (count > 0)
                {
                    //http://blog.csdn.net/Sandy945/article/details/5715583
                    //http://social.msdn.microsoft.com/Forums/zh-CN/295/thread/828fc65a-8371-4657-83c3-b8bf9682f000/

                    return "数据库中已存在该条记录!";
                }
                else
                {
                    OracleCommand insertCmd = new OracleCommand(sr.InsertStr, conn);
                    insertCmd.ExecuteNonQuery();
                    return "导入单条记录成功！";
                }
            }
            else
            {
                return "某个必填字段为空！";
            }
        }
        catch (Exception ex)
        {            
            return ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }
    public void ExcelImport(DataSet ds,string type)
    {
        DataRow[] dr = ds.Tables[0].Select();
        int rownum = ds.Tables[0].Rows.Count;

        if (rownum == 0)
        {
            ResultMessage = "Excel表为空，无数据！";
        }
        else
        {
            string connStr = WebConfigurationManager.ConnectionStrings["ConnectionStringForOracle"].ConnectionString;
            OracleConnection conn = new OracleConnection(connStr);
            
            int ErrorCount = 0;
            ArrayList ErrorList = new ArrayList();
            int InsertCount = 0;
            ArrayList InsertList = new ArrayList();
            int UpdateCount = 0;
            ArrayList UpdateList = new ArrayList();

            try
            {
                conn.Open();
                for (int i = 0; i < dr.Length; i++)
                {
                    RecordFactory rf = new RecordFactory();
                    SingleRecord sr = rf.CreatRecord(dr[i], type);// new RecordFactory(dr[i], type);
                    if (sr.isNotEmpty())
                    {
                        OracleCommand selectCmd = new OracleCommand(sr.SelectStr, conn);
                        int count = Convert.ToInt32(selectCmd.ExecuteScalar());
                        if (count > 0)
                        {
                            //OracleCommand updateCmd = new OracleCommand(sr.UpdateStr, conn);
                            //updateCmd.ExecuteNonQuery();
                            UpdateCount++;
                            UpdateList.Add((i + 1).ToString());
                        }
                        else
                        {
                            OracleCommand insertCmd = new OracleCommand(sr.InsertStr, conn);
                            insertCmd.ExecuteNonQuery();
                            InsertCount++;
                            InsertList.Add((i + 1).ToString());
                        }
                    }
                    else
                    {
                        ErrorCount++;//某个必填字段为空
                        ErrorList.Add((i + 1).ToString());
                    }
                }
                //循环结束
                ResultMessage = InsertCount.ToString() + "条数据导入成功" + UpdateCount.ToString() + "条数据重复！" + ErrorCount.ToString() + "条数据必填字段为空没有导入!";
                InsertMessage = "插入成功的行：";
                UpdateMessage = "数据重复的行：";
                ErrorMessage = "插入失败的行：";
                foreach (string s in InsertList)
                {
                    InsertMessage += " " + s;
                }
                foreach (string s in UpdateList)
                {
                    UpdateMessage += " " + s;
                }
                foreach (string s in ErrorList)
                {
                    ErrorMessage += " " + s;
                }
            }
            catch (Exception ex)
            {
                ResultMessage = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
    }    
}